﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace websitev2.Core
{
    public class GraphCore
    {


        public static string ReturnSQLforApp30Days(Int64 AppID, bool FilterByUser, Int64 UserID)
        {
            string _appID = AppID.ToString();

            string _returnSQL = @" 
                
                DECLARE @BeginDate  DATETIME;
DECLARE @EndDate    DATETIME;

SET @BeginDate  = DATEADD(MONTH, -1, GETDATE());
SET @EndDate    = GETDATE();

WITH CTE(DateRange) AS
(
        SELECT  @BeginDate  AS DateRange
    UNION ALL
        SELECT  DATEADD(DAY, 1, DateRange)
        FROM    CTE
        WHERE   DATEADD(DAY, 1, DateRange) <= @EndDate
)
SELECT          DATEADD(DAY, 0, DATEDIFF(DAY, 0, CTE.DateRange))    AS DateRange
            ,   COALESCE(UAL.ElapsedDuration, 0)                    AS ElapsedDuration
FROM            CTE
OUTER APPLY     (
                    SELECT  SUM(ElapsedTimeSeconds)/60 ElapsedDuration
                    FROM    dbo.UsersAppLog UAL
                    WHERE   DATEDIFF(DAY, UAL.LoggedDate, CTE.DateRange) = 0 and
                    AppID = " + _appID;

            // Enable this to be filtered by the user. 
            if (FilterByUser)
            {
                _returnSQL += " and UserID = " + UserID.ToString(); 
            }
            
           _returnSQL += " ) UAL;";


            return _returnSQL;
        }


        public static string ReturnSQLFor30DayUsageByType(Int64 UserID, int AppType, int Days)
        {

            string _return = @" 
                DECLARE @BeginDate  DATETIME;
DECLARE @EndDate    DATETIME;

SET @BeginDate  = DATEADD(Day, #days#, GETDATE());
SET @EndDate    = GETDATE();

WITH CTE(DateRange) AS
(
        SELECT  @BeginDate  AS DateRange
    UNION ALL
        SELECT  DATEADD(DAY, 1, DateRange)
        FROM    CTE
        WHERE   DATEADD(DAY, 1, DateRange) <= @EndDate
)
SELECT          DATEADD(DAY, 0, DATEDIFF(DAY, 0, CTE.DateRange))    AS DateRange
            ,   COALESCE(UAL.ElapsedDuration, 0)                    AS ElapsedDuration
FROM            CTE
OUTER APPLY     (
select SUM(ElapsedTimeSeconds)/60 ElapsedDuration  from  USersAppLog UAL where AppiD 
in (select Appid from WindowAppList where typeID=#type#) and USerID=#user# and
   DATEDIFF(DAY, UAL.LoggedDate, CTE.DateRange) = 0                    ) UAL  option (maxrecursion 365);

";
            _return = _return.Replace("#type#", AppType.ToString());
            _return = _return.Replace("#user#", UserID.ToString());
            _return = _return.Replace("#days#", "-" + Days.ToString());

            return _return;


        }

    }
}